﻿USE [ASPNETVN.PORTAL]
GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Collection_Delete]    Script Date: 06/19/2008 15:41:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_PhotoAlbum_Collection_Delete]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Thursday, June 19, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_PhotoAlbum_Collection_Delete]
	@ID int
AS

DELETE FROM 
	[dbo].[t_PhotoAlbum_Collections]
WHERE
	[ID] = @ID


GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Collection_DeleteDynamic]    Script Date: 06/19/2008 15:41:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_PhotoAlbum_Collection_DeleteDynamic]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Thursday, June 19, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_PhotoAlbum_Collection_DeleteDynamic]
	@WhereCondition nvarchar(500)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = 'DELETE FROM [dbo].[t_PhotoAlbum_Collections] WHERE ' + @WhereCondition

EXEC sp_executesql @SQL


GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Collection_Insert]    Script Date: 06/19/2008 15:41:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_PhotoAlbum_Collection_Insert]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Thursday, June 19, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_PhotoAlbum_Collection_Insert]
	@ModuleID int,
	@Title nvarchar(128),
	@IsPrivate bit,
	@Description nvarchar(512),
	@CreatedBy nvarchar(128),
	@CreationTime datetime,
	@ID int OUTPUT
AS
INSERT INTO [dbo].[t_PhotoAlbum_Collections]
(
	[ModuleID],
	[Title],
	[IsPrivate],
	[Description],
	[CreatedBy],
	[CreationTime]
)
VALUES 
(
	@ModuleID,
	@Title,
	@IsPrivate,
	@Description,
	@CreatedBy,
	@CreationTime
)

SET @ID = SCOPE_IDENTITY()


GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Collection_InsertUpdate]    Script Date: 06/19/2008 15:41:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_PhotoAlbum_Collection_InsertUpdate]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Thursday, June 19, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_PhotoAlbum_Collection_InsertUpdate]
	@ID int,
	@ModuleID int,
	@Title nvarchar(128),
	@IsPrivate bit,
	@Description nvarchar(512),
	@CreatedBy nvarchar(128),
	@CreationTime datetime
AS
IF EXISTS(SELECT [ID] FROM [dbo].[t_PhotoAlbum_Collections] WHERE [ID] = @ID)
	BEGIN
		UPDATE
			[dbo].[t_PhotoAlbum_Collections] 
		SET
			[ModuleID] = @ModuleID,
			[Title] = @Title,
			[IsPrivate] = @IsPrivate,
			[Description] = @Description,
			[CreatedBy] = @CreatedBy,
			[CreationTime] = @CreationTime
		WHERE
			[ID] = @ID
	END
ELSE
	BEGIN
		
		INSERT INTO [dbo].[t_PhotoAlbum_Collections]
		(
			[ModuleID],
			[Title],
			[IsPrivate],
			[Description],
			[CreatedBy],
			[CreationTime]
		)
		VALUES 
		(
			@ModuleID,
			@Title,
			@IsPrivate,
			@Description,
			@CreatedBy,
			@CreationTime
		)		
	END

GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Collection_Load]    Script Date: 06/19/2008 15:41:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_PhotoAlbum_Collection_Load]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Thursday, June 19, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_PhotoAlbum_Collection_Load]
	@ID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[ModuleID],
	[Title],
	[IsPrivate],
	[Description],
	[CreatedBy],
	[CreationTime]
FROM
	[dbo].[t_PhotoAlbum_Collections]
WHERE
	[ID] = @ID

GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Collection_SelectAll]    Script Date: 06/19/2008 15:41:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_PhotoAlbum_Collection_SelectAll]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Thursday, June 19, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_PhotoAlbum_Collection_SelectAll]
	@ModuleID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[ModuleID],
	[Title],
	[IsPrivate],
	[Description],
	[CreatedBy],
	[CreationTime]
FROM
	[dbo].[t_PhotoAlbum_Collections]	
WHERE 
	(ModuleID = @ModuleID)


GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Collection_SelectDynamic]    Script Date: 06/19/2008 15:41:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_PhotoAlbum_Collection_SelectDynamic]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Thursday, June 19, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_PhotoAlbum_Collection_SelectDynamic]
	@WhereCondition nvarchar(500),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = 
'SELECT 
	[ID],
	[ModuleID],
	[Title],
	[IsPrivate],
	[Description],
	[CreatedBy],
	[CreationTime]
FROM [dbo].[t_PhotoAlbum_Collections] 
WHERE ' + @WhereCondition

IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression
END

EXEC sp_executesql @SQL


GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Collection_SelectFullAll]    Script Date: 06/19/2008 15:41:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_PhotoAlbum_Collection_SelectAll]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Saturday, June 14, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_PhotoAlbum_Collection_SelectFullAll]
	@ModuleID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	C.[ID],
	C.[ModuleID],
	C.[Title],
	C.[Description],
	C.[CreatedBy],
	C.[CreationTime],
	P.FileName AS [CoverFileName]
	
FROM
	[dbo].[t_PhotoAlbum_Collections] C LEFT JOIN [dbo].[t_PhotoAlbum_Pictures] P ON C.ID = P.CollectionID AND P.IsCover = 1

WHERE 
	(C.ModuleID = @ModuleID)


GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Collection_SelectFullBy_ID]    Script Date: 06/19/2008 15:41:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_PhotoAlbum_Collection_SelectFullBy_ID]
(
	@ID INT
)
AS
BEGIN
	SELECT
		C.[ID],
		C.[ModuleID],
		C.[Title],
		C.[Description],
		C.[CreatedBy],
		C.[CreationTime],
		P.[FileName] AS 'CoverFileName',
		(SELECT COUNT([ID]) FROM [t_PhotoAlbum_Pictures] P1 WHERE P1.[CollectionID] = C.[ID] AND P1.[IsCover] = 0) AS 'Quantity'
		
	FROM
		[t_PhotoAlbum_Collections] C LEFT JOIN [t_PhotoAlbum_Pictures] P ON C.[ID] = P.[CollectionID] AND P.[IsCover] = 1
	WHERE
		C.[ID] = @ID
END


GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Collection_Update]    Script Date: 06/19/2008 15:41:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_PhotoAlbum_Collection_Update]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Thursday, June 19, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_PhotoAlbum_Collection_Update]
	@ID int,
	@ModuleID int,
	@Title nvarchar(128),
	@IsPrivate bit,
	@Description nvarchar(512),
	@CreatedBy nvarchar(128),
	@CreationTime datetime
AS

UPDATE
	[dbo].[t_PhotoAlbum_Collections]
SET
	[ModuleID] = @ModuleID,
	[Title] = @Title,
	[IsPrivate] = @IsPrivate,
	[Description] = @Description,
	[CreatedBy] = @CreatedBy,
	[CreationTime] = @CreationTime
WHERE
	[ID] = @ID


GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Picture_Delete]    Script Date: 06/19/2008 15:41:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_PhotoAlbum_Picture_Delete]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Saturday, June 14, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_PhotoAlbum_Picture_Delete]
	@ID int
AS

DELETE FROM 
	[dbo].[t_PhotoAlbum_Pictures]
WHERE
	[ID] = @ID


GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Picture_DeleteBy_CollectionID]    Script Date: 06/19/2008 15:41:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_PhotoAlbum_Picture_DeleteBy_CollectionID]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Saturday, June 14, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_PhotoAlbum_Picture_DeleteBy_CollectionID]
	@CollectionID int
AS

DELETE FROM [dbo].[t_PhotoAlbum_Pictures]
WHERE
	[CollectionID] = @CollectionID


GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Picture_DeleteDynamic]    Script Date: 06/19/2008 15:41:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_PhotoAlbum_Picture_DeleteDynamic]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Saturday, June 14, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_PhotoAlbum_Picture_DeleteDynamic]
	@WhereCondition nvarchar(500)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = 'DELETE FROM [dbo].[t_PhotoAlbum_Pictures] WHERE ' + @WhereCondition

EXEC sp_executesql @SQL


GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Picture_Insert]    Script Date: 06/19/2008 15:41:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_PhotoAlbum_Picture_Insert]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Saturday, June 14, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_PhotoAlbum_Picture_Insert]
	@CollectionID int,
	@Title nvarchar(128),
	@FileName nvarchar(256),
	@IsCover bit,
	@ID int OUTPUT
AS
INSERT INTO [dbo].[t_PhotoAlbum_Pictures]
(
	[CollectionID],
	[Title],
	[FileName],
	[IsCover]
)
VALUES 
(
	@CollectionID,
	@Title,
	@FileName,
	@IsCover
)

SET @ID = SCOPE_IDENTITY()


GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Picture_InsertUpdate]    Script Date: 06/19/2008 15:41:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_PhotoAlbum_Picture_InsertUpdate]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Saturday, June 14, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_PhotoAlbum_Picture_InsertUpdate]
	@ID int,
	@CollectionID int,
	@Title nvarchar(128),
	@FileName nvarchar(256),
	@IsCover bit
AS
IF EXISTS(SELECT [ID] FROM [dbo].[t_PhotoAlbum_Pictures] WHERE [ID] = @ID)
	BEGIN
		UPDATE
			[dbo].[t_PhotoAlbum_Pictures] 
		SET
			[CollectionID] = @CollectionID,
			[Title] = @Title,
			[FileName] = @FileName,
			[IsCover] = @IsCover
		WHERE
			[ID] = @ID
	END
ELSE
	BEGIN
		
		INSERT INTO [dbo].[t_PhotoAlbum_Pictures]
		(
			[CollectionID],
			[Title],
			[FileName],
			[IsCover]
		)
		VALUES 
		(
			@CollectionID,
			@Title,
			@FileName,
			@IsCover
		)		
	END

GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Picture_Load]    Script Date: 06/19/2008 15:41:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_PhotoAlbum_Picture_Load]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Saturday, June 14, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_PhotoAlbum_Picture_Load]
	@ID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[CollectionID],
	[Title],
	[FileName],
	[IsCover]
FROM
	[dbo].[t_PhotoAlbum_Pictures]
WHERE
	[ID] = @ID

GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Picture_SelectAll]    Script Date: 06/19/2008 15:41:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_PhotoAlbum_Picture_SelectAll]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Saturday, June 14, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_PhotoAlbum_Picture_SelectAll]
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[CollectionID],
	[Title],
	[FileName],
	[IsCover]
FROM
	[dbo].[t_PhotoAlbum_Pictures]	


GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Picture_SelectBy_CollectionID]    Script Date: 06/19/2008 15:41:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_PhotoAlbum_Picture_SelectBy_CollectionID]
	@CollectionID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[CollectionID],
	[Title],
	[FileName],
	[IsCover]
FROM
	[dbo].[t_PhotoAlbum_Pictures]
WHERE
	[CollectionID] = @CollectionID




GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Picture_SelectBy_CollectionID_NotInclude_CoverFileName]    Script Date: 06/19/2008 15:41:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_PhotoAlbum_Picture_SelectBy_CollectionID_NotInclude_CoverFileName]
	@CollectionID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[CollectionID],
	[Title],
	[FileName],
	[IsCover]
FROM
	[dbo].[t_PhotoAlbum_Pictures]
WHERE
	[CollectionID] = @CollectionID AND
	[IsCover] = 0




GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Picture_SelectDynamic]    Script Date: 06/19/2008 15:41:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_PhotoAlbum_Picture_SelectDynamic]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Saturday, June 14, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_PhotoAlbum_Picture_SelectDynamic]
	@WhereCondition nvarchar(500),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = 
'SELECT 
	[ID],
	[CollectionID],
	[Title],
	[FileName],
	[IsCover]
FROM [dbo].[t_PhotoAlbum_Pictures] 
WHERE ' + @WhereCondition

IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression
END

EXEC sp_executesql @SQL


GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Picture_SetCover]    Script Date: 06/19/2008 15:41:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_PhotoAlbum_Picture_SetCover]
(
	@ID INT
)
AS
BEGIN
	SET XACT_ABORT ON
	BEGIN TRANSACTION
		DECLARE @CollectionID INT
		SELECT @CollectionID = CollectionID FROM [t_PhotoAlbum_Pictures] WHERE [ID] = @ID
		UPDATE [t_PhotoAlbum_Pictures] SET [IsCover] = 0 WHERE [IsCover] = 1 AND CollectionID = @CollectionID
		UPDATE [t_PhotoAlbum_Pictures] SET [IsCover] = 1 WHERE [ID] = @ID
	COMMIT
END

GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Picture_Update]    Script Date: 06/19/2008 15:41:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_PhotoAlbum_Picture_Update]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Saturday, June 14, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_PhotoAlbum_Picture_Update]
	@ID int,
	@CollectionID int,
	@Title nvarchar(128),
	@FileName nvarchar(256),
	@IsCover bit
AS

UPDATE
	[dbo].[t_PhotoAlbum_Pictures]
SET
	[CollectionID] = @CollectionID,
	[Title] = @Title,
	[FileName] = @FileName,
	[IsCover] = @IsCover
WHERE
	[ID] = @ID


GO
/****** Object:  StoredProcedure [dbo].[p_PhotoAlbum_Picture_UpdateTitle]    Script Date: 06/19/2008 15:41:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_PhotoAlbum_Picture_UpdateTitle]
	@ID int,
	@Title nvarchar(128)
AS
BEGIN
	UPDATE t_PhotoAlbum_Pictures SET Title = @Title WHERE [ID] = @ID
END
